set more off
local a 46
local y 2004

*** EconActo from 2000 for forward feed
use "Data/NCDS/UKDA-5578-stata9_se/stata9_se/ncds6.dta" , clear
keep econact othacted ncdsid
sort ncdsid
save scratchdata/ncds_2004_forwardfeed.dta, replace

*** OUTCOMES FROM FLAT FILE FOR AGE 46
use "Data/NCDS/UKDA-5579-stata9/stata9/ncds7.dta", clear
sort ncdsid
merge 1:1 ncdsid using scratchdata/ncds_2004_forwardfeed.dta, keep(1 3) nogenerate
rename econact econact00 
rename othacted othacted00

rename ncdsid NCDSID

* merge RPI
ge year=`y'
merge m:1 year using scratchdata/RPI.dta, keep(match) nogenerate

* sex
ge sex_`a'=nd7sex

** Observation indicator
ge Obs_46=1

rename n7region region_`a'

********************************************************************************
* Part 1: pay period for employed
********************************************************************************

* gross
ge pp_gee=n7cgropd if n7cgropd>0
recode pp_gee (3=4) (4=4.33) (5=52) (97=.)

* net
ge pp_nee=n7cnetpd if n7cnetpd>0
recode pp_nee (3=4) (4=4.33) (5=52) (97=.)

********************************************************************************
* Part 2: pay for employed
********************************************************************************

* gross
ge gpee_`a'=n7cgropy if n7cgropy>=0

* net
ge npee_`a'=n7cnetpy if n7cnetpy>=0

********************************************************************************
* Part 3: pay per week for employed
********************************************************************************

* gross
replace gpee_`a'=gpee_`a'/pp_gee
drop pp_gee

* net
replace npee_`a'=npee_`a'/pp_nee
drop pp_nee

********************************************************************************
* Part 4: hours per week for employed
********************************************************************************

ge hoursee_`a'=n7chour1 if n7chour1>=0

********************************************************************************
* Part 5: weekly earnings for self-employed
********************************************************************************

ge ese_`a'=. 	/* information not collected*/
ge npse_`a'=.   /* information not collected*/

********************************************************************************
* Part 6: hours per week for self-employed
********************************************************************************

ge hoursse_`a'=n7cjsehs if n7cjsehs>=0

********************************************************************************
* Part 7: inflation adjustment
********************************************************************************

local vars npee_`a' gpee_`a' ese_`a' npse_`a'
foreach v of local vars {
replace `v'=`v'/rpi
}

********************************************************************************
* Part 8: Economic activity
********************************************************************************

rename nd7ecact EconAct

* use backcode for current economic activity
replace EconAct=nd7otha1 if nd7otha1<5 & EconAct==97 & nd7otha1>0
replace EconAct=1 if  nd7otha1==12 & EconAct==97
replace EconAct=3 if  nd7otha1==14 & EconAct==97

* replace with activity reported in 2000 if no change
replace EconAct=econact00 if (EconAct==.|EconAct==97) & n7eclast==1
replace EconAct=othacted00 if othacted00<5 & (EconAct==.|EconAct==97|EconAct==12) & n7eclast==1
replace EconAct=1 if othacted00==12 & (EconAct==.|EconAct==97|EconAct==12) & n7eclast==1
replace EconAct=3 if othacted00==13 & (EconAct==.|EconAct==97|EconAct==12) & n7eclast==1

ge EE_`a'=(EconAct==1|EconAct==2) if EconAct!=. 
ge SE_`a'=(EconAct==3|EconAct==4) if EconAct!=. 


* clasify as non-working if report zero earnings
replace EE_`a'=0 if npee_`a'==0 & EE_`a'!=.
replace SE_`a'=0 if ese_`a'==0  & SE_`a'!=.
ge AE_`a'=EE_`a'+SE_`a'

ge UN_`a'=EconAct==5 if EconAct!=.

********************************************************************************
* Part 9: recode hours
********************************************************************************

* recode hours to missing if hours>112 or zero
replace hoursee_`a'=. if (hoursee_`a'>112|hoursee_`a'==0)
replace hoursse_`a'=. if (hoursse_`a'>112|hoursse_`a'==0)

replace hoursee_`a'=40 if EconAct==1 & hoursee_`a'==.
replace hoursee_`a'=20 if EconAct==2 & hoursee_`a'==.
replace hoursse_`a'=40 if EconAct==3 & hoursse_`a'==.
replace hoursse_`a'=20 if EconAct==4 & hoursse_`a'==.

replace hoursee_`a'=. if  npee_`a'==.
replace hoursse_`a'=. if  ese_`a'==.

* recode earnings and hours as missing if don't match current economic activity
replace npee_`a'=. if EE_`a'!=1
replace gpee_`a'=. if EE_`a'!=1
replace hoursee_`a'=. if  EE_`a'!=1
replace ese_`a'=. if SE_`a'!=1
replace hoursse_`a'=. if SE_`a'!=1


********************************************************************************
* Part 10: hourly wages
********************************************************************************

* employed net
ge wageee_`a'=npee_`a'/hoursee_`a'

* employed gross
ge gwageee_`a'=gpee_`a'/hoursee_`a'

* self-employed (earnings)
ge wagese_`a'=ese_`a'/hoursse_`a'

* self-employed (profit) 
ge prose_`a'=npse_`a'/hoursse_`a'

********************************************************************************
* Part 11: combine employed and self-employed
********************************************************************************

egen wage_`a'=rowmax(wageee_`a' wagese_`a')
drop wageee_`a' wagese_`a'

egen pay_`a'=rowmax(npee_`a' ese_`a')
drop npee_`a' ese_`a'

egen hours_`a'=rowmax(hoursee_`a' hoursse_`a')
drop hoursee_`a' hoursse_`a'

********************************************************************************
*  Part 12: log variables
********************************************************************************

local vars wage pay hours
foreach v of local vars {
ge l`v'_`a'=ln(`v'_`a')
}

********************************************************************************
* Part 13: SOC 1990 and 2000
********************************************************************************

ge soc90_`a'=n7soc90 if n7soc90>0
replace soc90_`a'=. if AE_`a'!=1


ge soc00_`a'=n7xso000  if n7xso000 >0
replace soc00_`a'=. if AE_`a'!=1


********************************************************************************
* Part 14: Occupational attainment
********************************************************************************

rename n7sc SC_`a'
replace SC_`a'=. if SC_`a'<1|SC_`a'==6|AE_`a'!=1

********************************************************************************
* Part 15: Job security
********************************************************************************

ge JobSecurity_`a'=.

********************************************************************************
* Part 16: Industry
********************************************************************************

rename n7sic92 Ind_`a' /* 1992 code */
replace Ind_`a'=. if Ind_`a'<0|AE_`a'!=1

ge twodigit=Ind_`a'
ge GL="A" if twodigit<=02 						/* Agriculture And Forestry */
replace GL="B" if twodigit==05  				/* Fishing */
replace GL="C" if twodigit>=10 & twodigit<=14 	/* Mining And Quarrying*/
replace GL="D" if twodigit>=15 & twodigit<=37  	/* Manufacturing */
replace GL="E" if twodigit>=40 & twodigit<=41  	/* Electricity, Gas And Water Supply */
replace GL="F" if twodigit==45  				/* Construstion */
replace GL="G" if twodigit>=50 & twodigit<=52  	/* Wholesale And Retail Trade; Repair Of Motor Vehicles, Motorcycles And Personal and Household Goods */
replace GL="H" if twodigit==55  				/* Hotels And Restaurants */
replace GL="I" if twodigit>=60 & twodigit<=64  	/* Transport, Storage And Communication  */
replace GL="J" if twodigit>=65 & twodigit<=67  	/* Financial Activities */
replace GL="K" if twodigit>=70 & twodigit<=74  	/* Property Development, Renting, Business And Research Activities */
replace GL="L" if twodigit==75  				/* Public Administration And Defence; Social Security */
replace GL="M" if twodigit==80 					/* Education */
replace GL="N" if twodigit==85 					/* Health And Social Work */
replace GL="O" if twodigit>=90 & twodigit<=93  	/* Other Community, Social And Personal Service Activities */
replace GL="P" if twodigit==95  				/* Private Households With Employed Persons */
replace GL="Q" if twodigit==99 					/* International Organisations And Bodies */

rename GL GL_`a'

rename  nd7wpc0*  nd7wpc0*_`a'
rename nd7joe01 nd7joe01_`a'
********************************************************************************
* Finish up
********************************************************************************

keep NCDSID *_`a'
sort NCDSID
rename *_`a' *_I`a'

save scratchdata/ncds_2004_flatfile.dta, replace
